﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel;
namespace Mesuro.DataManagement {

	public class Database : INotifyPropertyChanged {
		public const string COL_ID = "sweep_id";
		public const string COL_DESCRIPTION = "description";
		public const string COL_TIMESTAMP = "timestamp";
		public const string COL_FREQUENCY = "frequency";
		public const string COL_HARMONICS = "harmonics";
		public const string COL_BIAS1 = "bias1";
		public const string COL_BIAS2 = "bias2";
		public const string COL_DC_MEASURED = "dcmeasured";
		public const string COL_METADATA_PATH = "metadata_filepath";

		private string dataSource = "localhost";
		public string DataSource {
			get { return dataSource; }
			set { dataSource = value; }
		}

		private string databaseName = "mesuro";
		public string DatabaseName {
			get { return databaseName; }
			set { databaseName = value; }
		}

		private string username = "root";
		public string Username {
			get { return username; }
			set { username = value; }
		}

		private string password = "";
		public string Password {
			get { return password; }
			set { password = value; }
		}

		private int port = 3306;
		public int Port {
			get { return port; }
			set { port = value; }
		}

		private string defaultTableName = "sweeps";
		public string DefaultTableName {
			get { return defaultTableName; }
			set { defaultTableName = value; PropertyChanged( this, new PropertyChangedEventArgs( PROP_DefaultTableName ) ); }
		}

		private bool encrypt = true;
		public bool Encrypt {
			get { return encrypt; }
			set { encrypt = value; }
		}

		private bool persistSecurity = false;
		public bool PersistSecurity {
			get { return persistSecurity; }
			set { persistSecurity = value; }
		}

		private bool logging = false;
		public bool Logging {
			get { return logging; }
			set { logging = value; }
		}

		public bool Connected {
			get {
				return connToDb != null && connToDb.State.HasFlag( ConnectionState.Open );
			}
		}

		private MySqlConnection connToDb;

		public Exception LastError { get; private set; }

		public Database() { }
		public Database( string nDataSource, string nDatabase, string nUsername, string nPassword ) {
			this.DataSource = nDataSource;
			this.DatabaseName = nDatabase;
			this.Username = nUsername;
			this.Password = nPassword;
		}

		public bool EnsureConnnected() {
			return Connected || Connect();
		}
		public bool Connect() {
			if (Connected)
				throw new InvalidOperationException( "Cannot connect to database; already connected!" );

			string conn = String.Format( "Data source={0};Port={1};Database={2};UserId={3};Password={4};Persist Security Info={5};Encrypt={6};Logging={7};Convert Zero Datetime=true",
				DataSource, Port, DatabaseName, Username, Password, PersistSecurity, Encrypt, Logging );

			if (connToDb == null)
				connToDb = new MySqlConnection();

			connToDb.ConnectionString = conn;
			try {
				connToDb.Open();
			} catch (Exception ex) {
				LastError = ex;
			}

			PropertyChanged( this, new PropertyChangedEventArgs( PROP_Connected ) );

			return connToDb.State.HasFlag( ConnectionState.Open );
		}
		public void Disconnect() {
			if (connToDb != null)
				connToDb.Close();

			PropertyChanged( this, new PropertyChangedEventArgs( PROP_Connected ) );
		}

		public String[] ListTables() {
			if (!EnsureConnnected()) throw new InvalidOperationException( "Cannot connect to database." );

			MySqlCommand command = connToDb.CreateCommand();
			command.CommandText = "SHOW TABLES;";

			List<String> tables = new List<string>();

			lock (connToDb) {
				MySqlDataReader reader = command.ExecuteReader();

				try {
					while (reader.Read())
						tables.Add( reader.GetString( 0 ) );

				} finally {
					reader.Close();
				}
			}
			return tables.ToArray();
		}

		public void InsertExperiment( ExperimentHeader header ) {

			if (!EnsureConnnected()) throw new InvalidOperationException( "Cannnot connect to database." );

			MySqlCommand command = connToDb.CreateCommand();

			string metafile = header.MetadataPath.Replace( @"\", "/" );

			command.CommandText = String.Format( "INSERT INTO {0} (description,timestamp,frequency,harmonics,bias1,bias2,DCMeasured,metadata_Filepath) VALUES('{8}','{1}',{2},{3},{4},{5},{6},'{7}');",
				defaultTableName, header.Timestamp, header.Frequency, header.Harmonics, header.Bias1, header.Bias2, header.DcMeasured, metafile,header.Description );

			lock (connToDb)
				command.ExecuteNonQuery();
		}

		public ExperimentHeader[] ListExperiments() {
			List<ExperimentHeader> headers = new List<ExperimentHeader>();

			if (!EnsureConnnected()) throw new InvalidOperationException( "Cannot connect to database." );

			MySqlCommand command = connToDb.CreateCommand();
			command.CommandText = "Select * from " + defaultTableName + ";";

			lock (connToDb) {
				MySqlDataReader reader = command.ExecuteReader();
				try {
					while (reader.Read()) {
						ExperimentHeader tmp = new ExperimentHeader();

						int y = 0;
						if (int.TryParse( reader[COL_ID].ToString(), out y ))
							tmp.ID = y;

						tmp.Description = reader.GetString( COL_DESCRIPTION );

						tmp.Timestamp = reader[COL_TIMESTAMP].ToString();

						double x = 0.0;
						if (double.TryParse( reader[COL_FREQUENCY].ToString(), out x ))
							tmp.Frequency = x;

						if (int.TryParse( reader[COL_HARMONICS].ToString(), out y ))
							tmp.Harmonics = y;

						if (int.TryParse( reader[COL_BIAS1].ToString(), out y ))
							tmp.Bias1 = y;

						if (int.TryParse( reader[COL_BIAS2].ToString(), out y ))
							tmp.Bias2 = y;

						if (int.TryParse( reader[COL_DC_MEASURED].ToString(), out y ))
							tmp.DcMeasured = y;

						string metapath = reader[COL_METADATA_PATH].ToString();

						tmp.MetadataPath = metapath.Replace( "/", @"\" );

						headers.Add( tmp );
					}
				} finally {
					reader.Close();
				}
			}

			return headers.ToArray();
		}

		internal void Update( ExperimentHeader header ) {
			if (header.ID == null) {
				InsertExperiment( header );
				return;
			}

			if (!EnsureConnnected()) throw new InvalidOperationException( "Cannot connect to database." );

			StringBuilder commandBuilder = new StringBuilder( "UPDATE " );
			commandBuilder.Append( defaultTableName ).Append( " SET " );
			commandBuilder.AppendFormat( "{0}='{1}',", COL_DESCRIPTION, header.Description );
			commandBuilder.AppendFormat( "{0}='{1}',", COL_TIMESTAMP, header.Timestamp );
			commandBuilder.AppendFormat( "{0}={1},", COL_FREQUENCY, header.Frequency );
			commandBuilder.AppendFormat( "{0}={1},", COL_HARMONICS, header.Harmonics );
			commandBuilder.AppendFormat( "{0}={1},", COL_BIAS1, header.Bias1 );
			commandBuilder.AppendFormat( "{0}={1},", COL_BIAS2, header.Bias2 );
			commandBuilder.AppendFormat( "{0}={1},", COL_DC_MEASURED, header.DcMeasured );
			commandBuilder.AppendFormat( "{0}='{1}'", COL_METADATA_PATH, header.MetadataPath );
			commandBuilder.AppendFormat( " WHERE {0}={1}", COL_ID, header.ID );
			MySqlCommand command = connToDb.CreateCommand();
			command.CommandText = commandBuilder.ToString();

			lock (connToDb) {
				command.ExecuteNonQuery();
			}
		}
		//Delete row--

		public void CreateFullBackup() {

		}

		public event PropertyChangedEventHandler PropertyChanged;
		public const string PROP_Connected = "Connected";
		public const string PROP_DefaultTableName = "DefaultTableName";
	}

}